package com.eece496.Database;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import com.eece496.Helpers.CourseInfo;
import com.eece496.Helpers.GradesInfo;
import com.eece496.Helpers.StudentGradeInfo;

public class Grades extends Database {

	private String assignmentName;
	//private String name;
	private String courseNumber;
	private String sectionNumber;
	private int studentID;
	private int indGrade; // leave out for now
	private int groupGrade; // leave out for now
	private String grade;
	private String gradeType;
	//private String comments;

	public Grades() {
		tableName = "Grades";
	}

	public Grades(String assignmentName, String courseNumber,
			String sectionNumber, int studentID, String grade, String gradeType) {
		tableName = "Grades";

		this.assignmentName = assignmentName;
		this.courseNumber = courseNumber;
		this.sectionNumber = sectionNumber;
		this.studentID = studentID;
		this.grade = grade;
		this.gradeType = gradeType;
	//	this.indGrade = indGrade;
	//	this.groupGrade = groupGrade;
	//	this.comments = comments;
	}

	@Override
	public void Insert() throws SQLException {

		Statement stmt = connection.createStatement();

		String sqlString = "INSERT INTO " + tableName + " VALUES ("
				+ AddQuotes(assignmentName) + ", " 
				+ AddQuotes(courseNumber) + ", " + AddQuotes(sectionNumber)
				+ ", " + studentID + ", " + AddQuotes(grade) + ", " + AddQuotes(gradeType) + ")";
		stmt.executeUpdate(sqlString);
	//	System.out.println(sqlString);

	}

	@Override
	public void Delete(String primary) throws SQLException {
		Statement stmt = connection.createStatement();

		String sqlString = "DELETE from " + tableName + " WHERE studentID = "
				+ studentID + " and courseNumber = " + AddQuotes(courseNumber)
				+ " and assignmentName = " + AddQuotes(assignmentName);
		stmt.executeUpdate(sqlString);

	}

	public void DeleteStudentGrades(int studentID, String courseNumber, String sectionNumber) throws SQLException
	{
		Statement stmt = connection.createStatement();

		String sqlString = "DELETE from " + tableName + " WHERE studentID = "
				+ studentID + " and courseNumber = " + AddQuotes(courseNumber)
				+ " and sectionNumber = " + AddQuotes(sectionNumber);
		stmt.executeUpdate(sqlString);
	}
	
	
	
	@Override
	public void Display() throws SQLException {
		String sqlString = "SELECT * FROM " + tableName;
		Statement stmt = connection.createStatement();

		ResultSet rs = stmt.executeQuery(sqlString);

		while (rs.next()) {
			System.out.println(rs.getString("assignmentID") + " "
					+ rs.getString("studentID") + " "
					+ rs.getString("courseNumber") + " "
					+ rs.getString("sectionNumber") + " "
					+ rs.getString("indGrade") + " "
					+ rs.getString("groupGrade") + " "
					+ rs.getString("comments") + " ");
		}

		stmt.executeUpdate(sqlString);
	}

	public String GetGradeFromStudent(int studentID, String COGS, String courseNumber, String sectionNumber, String gradeType, String name) throws SQLException{
		String str = null;
		
		Statement stmt = connection.createStatement();
		
		String sqlString = "select grade from grades inner join taduties on "+
		" taduties.studentid =grades.studentid where COGSName = " + AddQuotes(COGS) + " and grades.courseNumber = " +
				AddQuotes(courseNumber) + " and grades.sectionNumber = " + AddQuotes(sectionNumber) + " and gradeType = " +
		AddQuotes(gradeType) + " and grades.studentID = " + studentID + " and grades.assignmentname like " + "'%"  + name + "%'";
		
		System.out.println(sqlString);
		ResultSet rs = stmt.executeQuery(sqlString);
		
		while(rs.next())
		{
			str = rs.getString("grade");
		}
		return str;
	}
	
	// Dont Delete- added by Steven Chow
	public ArrayList<StudentGradeInfo> GetAllGradesFromStudent(int studentID,
			String courseNumber, String sectionNumber) throws SQLException {
		ArrayList<StudentGradeInfo> list = new ArrayList<StudentGradeInfo>();

		Statement stmt = connection.createStatement();

		String sqlString = "select cogsassignments.name, cogsassignments.weight, Grades.grade "
				+ " from COGSAssignments inner join "
				+ tableName
				+ " on COGSAssignments.name = Grades.assignmentname"
				+ " where Grades.studentID = "
				+ studentID
				+ " and Grades.courseNumber = "
				+ AddQuotes(courseNumber)
				+ " and Grades.sectionNumber = " + AddQuotes(sectionNumber);

		System.out.println(sqlString);

		ResultSet rs = stmt.executeQuery(sqlString);

		while (rs.next()) {
			StudentGradeInfo info = new StudentGradeInfo();

			info.name = rs.getString("name");
			info.weight = rs.getInt("weight");
			info.grade = rs.getString("grade");
			list.add(info);
		}

		return list;
	}
	
	public ArrayList<String> GetGradeType(String courseNumber, String sectionNumber, int studentID, String gradeType) throws SQLException
	{
		ArrayList<String> list = new ArrayList<String>();
		Statement stmt = connection.createStatement();
		
		String sqlString = "select grade from grades where courseNumber= " + AddQuotes(courseNumber) + " and sectionNumber = "
				+ AddQuotes(sectionNumber) + " and studentID = " + studentID + " and gradeType = " + AddQuotes(gradeType); 
		
		ResultSet rs = stmt.executeQuery(sqlString);
		
		while(rs.next())
		{
			list.add(rs.getString("grade"));
		}
		
		return list;
	}

	// Don't Delete- Added by Steven Chow
	public void DeleteCourseGrades(String courseNumber, String sectionNumber)
			throws SQLException {
		Statement stmt = connection.createStatement();

		String sqlString = "DELETE from " + tableName
				+ " WHERE courseNumber = " + AddQuotes(courseNumber)
				+ " and sectionNumber = " + AddQuotes(sectionNumber);
		stmt.executeUpdate(sqlString);
	}

	// Don't Delete- Added by Steven Chow
	public ArrayList<GradesInfo> GetAllGradesFromCourse(String courseNumber,
			String sectionNumber) throws SQLException {
		ArrayList<GradesInfo> list = new ArrayList<GradesInfo>();

		// Grab all student info from enrollment
		Statement stmt = connection.createStatement();

		String sqlString = "select Student.studentID, Student.firstname, Student.lastname "
				+ " from Student inner join "
				+ " Enrollment"
				+ " on Student.studentID = Enrollment.studentID"
				+ " where Enrollment.courseNumber = "
				+ AddQuotes(courseNumber)
				+ " and Enrollment.sectionNumber = " + AddQuotes(sectionNumber);

		ResultSet rs = stmt.executeQuery(sqlString);
		// grab all grades for each student
		while (rs.next()) {
			GradesInfo info = new GradesInfo();

			info.studentID = rs.getInt("studentID");
			info.firstName = rs.getString("firstName");
			info.lastName = rs.getString("lastName");
			list.add(info);
		}

		for (GradesInfo info : list) {
			String sqlStringOne = "select Grades.grade from " + tableName
					+ " where studentID= " + info.studentID
					+ " and CourseNumber = " + AddQuotes(courseNumber)
					+ " and sectionNumber= " + AddQuotes(sectionNumber) + " order by gradeType desc";

			ResultSet rs1 = stmt.executeQuery(sqlStringOne);

			ArrayList<String> temp = new ArrayList<String>();

			while (rs1.next()) {

				temp.add(rs1.getString("grade"));
			}

			info.grades = temp;

		}
		return list;
	}
	
	public ArrayList<String> GetAssignments(String courseNumber, String sectionNumber) throws SQLException
	{
		ArrayList<String> list = new ArrayList<String>();
		
		Statement stmt = connection.createStatement();
		
		String sqlString = "select AssignmentName from grades where courseNumber= " + AddQuotes(courseNumber) + " and sectionNumber = "
				+ AddQuotes(sectionNumber) + " group by AssignmentName"; 
		
		ResultSet rs = stmt.executeQuery(sqlString);
		
		while(rs.next())
		{
			list.add(rs.getString("AssignmentName"));
		}
		
		return list;
	}
	
	
	public void ModifyGrade(String value, String courseNumber, String sectionNumber, Integer studentID, String assignmentName) throws SQLException
	{
		Statement stmt = connection.createStatement();
		String sqlString = "UPDATE " + tableName + " SET " + "grade" + " = " + AddQuotes(value) + " WHERE courseNumber = " + AddQuotes(courseNumber)
				+ " and sectionNumber= " + AddQuotes(sectionNumber) + " and studentID = " + studentID + " and assignmentName = " +
				AddQuotes(assignmentName);
		stmt.executeUpdate(sqlString);
		System.out.println(sqlString + " complete");			
	}
}